package com.ifast.generator;


import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * 操作Excel表格的功能类
 */
public class ExcelReader {

	/**
	 * 读取Excel数据内容
	 * @param is
	 * @return Map 包含单元格数据内容的Map对象
	 */
	public static String  readExcel(InputStream is) {
        StringBuilder sb = new StringBuilder();
        System.out.println(" /**读取excel生成创建表结构sql开始**/");
        sb.append(" /*\r\n");
        sb.append("Navicat Premium Data Transfer\r\n");

        sb.append("Source Server         : 183.131.52.83数据库\r\n");
        sb.append("Source Server Type    : MySQL\r\n");
        sb.append("Source Server Version : 50720\r\n");
        sb.append("Source Host           : 183.131.52.83:3306\r\n");
        sb.append("Source Schema         : shjyrc\r\n");

        sb.append("Target Server Type    : MySQL\r\n");
        sb.append("Target Server Version : 50720\r\n");
        sb.append("File Encoding         : 65001\r\n");

        sb.append(" Date: 11/07/2018 15:23:31\r\n");
        sb.append("*/\r\n");

        sb.append("SET NAMES utf8mb4;\r\n");
        sb.append("SET FOREIGN_KEY_CHECKS = 0;\r\n");
		try {
			POIFSFileSystem fs = new POIFSFileSystem(is);
			HSSFWorkbook wb = new HSSFWorkbook(fs);

			for (int j = 0; j < wb.getNumberOfSheets(); j++) {

				HSSFSheet sheet = wb.getSheetAt(j);
                String tableName = sheet.getSheetName();

                tableName = covertCh2En(tableName);//汉字表名转英文表名
                System.out.println("sheet页"+sheet.getSheetName()+"开始转化生成表"+tableName);
                sb.append("\r\n");
                sb.append("\r\n");
                sb.append("-- ----------------------------\r\n");
				sb.append("-- Table structure for "+tableName+"\r\n");
				sb.append("-- ----------------------------\r\n");
				sb.append("\r\n");
				sb.append("\r\n");
				sb.append("DROP TABLE IF EXISTS `"+tableName+"`;\r\n");
				sb.append("CREATE TABLE `"+tableName+"`  (\r\n");
				sb.append("`id` bigint(32) NOT NULL AUTO_INCREMENT COMMENT '主键ID',\r\n");//设置主键Long类型自增
				//sb.append("`id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '主键ID',\r\n");
				for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
					if(checkIsBlankRow(sheet.getRow(i))){//空行跳过
						continue;
					}

					HSSFCell cell6 = sheet.getRow(i).getCell(0);
					String cols6 = getStringCellValue(cell6);//字段名称

					HSSFCell cell7 = sheet.getRow(i).getCell(1);
					String cols7 = getStringCellValue(cell7).toLowerCase();//字段类型

					HSSFCell cell8 = sheet.getRow(i).getCell(2);
					String cols8 = getStringCellValue(cell8).replaceAll("\\s","")
                            .replaceAll("\\n","")
                            .replaceAll("\\t","")
                            .replaceAll("\\r","");//字段说明

					if(cols7.contains("varchar")){
						sb.append("`"+cols6+"` varchar("+cols7.substring(cols7.indexOf("(")+1, cols7.indexOf(")"))+") CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '"+cols8+"',\r\n");
					}else if(cols7.contains("date")){
						sb.append("`"+cols6+"` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '"+cols8+"',\r\n");
					}else if(cols7.contains("int")){
						sb.append("`"+cols6+"` int(10) NULL DEFAULT NULL COMMENT '"+cols8+"',\r\n");
					}else if(cols7.contains("text")){
						sb.append("`"+cols6+"` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '"+cols8+"',\r\n");
					}
				}
				sb.append("`createtime` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',\r\n");
				sb.append("`updatetime` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',\r\n");
				//sb.append("`optperson` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作人',\r\n");
				sb.append("`optperson` bigint(25) NULL DEFAULT NULL COMMENT '操作人',\r\n");
				sb.append("PRIMARY KEY (`id`) USING BTREE \r\n");
				sb.append(") ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;\r\n");
                System.out.println("========sql生成结束.表【"+tableName+"】");
			}

		} catch (IOException e) {
			e.printStackTrace();
		}
        sb.append("SET FOREIGN_KEY_CHECKS = 1;\r\n");

        System.out.println(" /**读取excel生成创建表结构sql结束**/\r\n");
		return sb.toString();
	}

	//转换表名
    private static String covertCh2En(String oldTableName){
        if(oldTableName.contains("基本信息")){//1.1教师基本信息
            return prefix+ "t_1basicinfo";
        }else if(oldTableName.contains("学习经历")){//1.2.学习经历
            return prefix+ "t_2learninfo";
        }else if(oldTableName.contains("教师履历")){//1.3-1教师履历
            return prefix+ "t_3resumeinfo";
        }else if(oldTableName.contains("工作经历")){//1.3.工作经历
            return prefix+ "t_3workinfo";
        }else if(oldTableName.contains("岗位聘任")){//1.4. 岗位聘任
            return prefix+ "t_4postengagem";
        }else if(oldTableName.contains("专业技术职务聘任")){//1.5.专业技术职务聘任
            return prefix+ "t_5profetech";
        }else if(oldTableName.contains("基本待遇")){//1.6.基本待遇
            return prefix+ "t_6basetreatment";
        }else if(oldTableName.contains("年度考核")){//1.7.年度考核
            return prefix+ "t_7annualassess";
        }else if(oldTableName.contains("教师资格")){//1.8. 教师资格
            return prefix+ "t_8teaqualification";
        }else if(oldTableName.contains("师德信息－考核信息")){//1.9. 师德信息－考核信息
            return prefix+ "t_9moralityassess";
        }else if(oldTableName.contains("师德信息－荣誉信息")){//1.10.师德信息－荣誉信息
            return prefix+ "t_10moralityhonor";
        }else if(oldTableName.contains("师德信息－处分信息")){//1.11.师德信息－处分信息
            return prefix+ "t_11moralitypunish";
        }else if(oldTableName.contains("教育教学")){//1.12.教育教学
            return prefix+ "t_12eduteach";
        }else if(oldTableName.contains("教学科研成果及获奖－项目(课题)")){//1.13.教学科研成果及获奖－项目(课题)
            return prefix+ "t_13awardpro";
        }else if(oldTableName.contains("教学科研成果及获奖－著作")){//1.14.教学科研成果及获奖－著作
            return prefix+ "t_14awardbook";
        }else if(oldTableName.contains("教学科研成果及获奖－论文")){//1.15.教学科研成果及获奖－论文
            return prefix+ "t_15awardthesis";
        }else if(oldTableName.contains("教学科研成果及获奖－奖励")){//1.16.教学科研成果及获奖－奖励
            return prefix+ "t_16awardreward";
        }else if(oldTableName.contains("文艺作品")){//1.17.教学科研成果及获奖－文艺作品
            return prefix+ "t_17awardart";
        }else if(oldTableName.contains("专利或软件著作权")){//1.18.教学科研成果及获奖－专利或软件著作权
            return prefix+ "t_18awardpatent";
        }else if(oldTableName.contains("咨询报告或研究报告")){//1.19.教学科研成果及获奖－咨询报告或研究报告
            return prefix+ "t_19awardreport";
        }else if(oldTableName.contains("国家医药证书")){//1.20.教学科研成果及获奖－国家医药证书
            return prefix+ "t_20awardmedcert";
        }else if(oldTableName.contains("国家标准或行业标准")){//1.21.教学科研成果及获奖－国家标准或行业标准
            return prefix+ "t_21awardstandard";
        }else if(oldTableName.contains("指导学生参加竞赛获奖")){//1.22.教学科研成果及获奖－指导学生参加竞赛获奖
            return prefix+ "t_22awardguidstuwin";
        }else if(oldTableName.contains("文教结合项目或其它")){//1.23.教学科研成果及获奖—文教结合项目或其它
            return prefix+ "t_23awardculeduoth";
        }else if(oldTableName.contains("入选人才项目")){//1.24.入选人才项目
            return prefix+ "t_24talentproject";
        }else if(oldTableName.contains("国内培训")){//1.25.国内培训
            return prefix+ "t_25domestictraining";
        }else if(oldTableName.contains("海外研修(访学)")){//1.26.海外研修(访学)
            return prefix+ "t_26overseasstudy";
        }else if(oldTableName.contains("技能及证书－语言能力")){//1.27.技能及证书－语言能力
            return prefix+ "t_27skilllanguage";
        }else if(oldTableName.contains("技能及证书－其他技能")){//1.28.技能及证书－其他技能
            return prefix+ "t_28skilloth";
        }else if(oldTableName.contains("技能及证书－证书信息")){//1.29.技能及证书－证书信息
            return prefix+ "t_29skillcert";
        }else if(oldTableName.contains("联系方式")){//1.30.联系方式
            return prefix+ "t_30contactinfo";
        }else if(oldTableName.contains("交流轮岗")){//1.30.交流轮岗
            return prefix+ "t_29exchange";
        }else if(oldTableName.contains("变动信息")){//1.31教师变动信息
            return prefix+ "t_31changeinfo";
        }else if(oldTableName.contains("绩效工资信息")){//1.32绩效工资信息
            return prefix+ "t_32meritpay";
        }else if(oldTableName.contains("家庭信息")){//1.4.家庭信息
            return prefix+ "t_4familyinfo";
        }else if(oldTableName.contains("退休待遇")){//1.5.退休待遇
            return prefix+ "t_5retirepay";
        }

        else{//没有匹配到
            return prefix+ oldTableName;
        }

    }

	// 检查当前行是否是空行
	private static boolean  checkIsBlankRow(HSSFRow row) {
		boolean isBlank = false;
		int blankCellNum =0;
		if(row==null){
			isBlank = true;
		}else{
			for(int i=0;i<1;i++){
				if(StringUtils.isNotBlank(getStringCellValue(row.getCell(i)))) {
					break;
				}else{
					blankCellNum++;
				}
			}
			if(blankCellNum==1){
				isBlank = true;
			}
		}
		return isBlank;
	}

	/**
	 * 获得单元格字符串
	 */
	private static  String getStringCellValue(HSSFCell cell) {
		if (cell == null){
			return null;
		}
		String result = "";
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_BOOLEAN://导入时如果为BOOLEAN型
				result = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					java.text.SimpleDateFormat TIME_FORMATTER = new java.text.SimpleDateFormat("yyyy-MM-dd");
					result = TIME_FORMATTER.format(cell.getDateCellValue());
				}
				else{
					try{
						result = new DecimalFormat("0").format(cell.getNumericCellValue());
						//result = String.valueOf(cell.getNumericCellValue());
					}catch(Exception e){
						result = cell.getRichStringCellValue().getString();
					}
				}
				break;
			case HSSFCell.CELL_TYPE_STRING:
				if (cell.getRichStringCellValue() == null){
					result = null;
				}else{
					result = cell.getRichStringCellValue().getString();
				}
				break;
			case HSSFCell.CELL_TYPE_BLANK://导入时如果为空
				result = null;
				break;
			case HSSFCell.CELL_TYPE_FORMULA://导入时如果为公式生成的数据
				try{
					result = String.valueOf(cell.getNumericCellValue());
				}catch(Exception e){
					result = cell.getRichStringCellValue().getString();
				}
				break;
			default:
				result = "";
		}
		if(StringUtils.isNotBlank(result)){
			result = result.trim();
		}
		return result;
	}

    static String prefix = "jyrc_"+"common"+"_";//表前缀 教育人才_类型_
	public static void main(String[] args) {
		try {
			InputStream is = new FileInputStream("D:\\使用文件\\重要文件\\上海教育人才服务中心\\数据标准V8.0\\退休民办\\民办教师数据标准.xls");
            File file = new File("D:\\使用文件\\重要文件\\上海教育人才服务中心\\数据标准V8.0\\退休民办\\民办教师数据标准.sql");
            FileUtils.writeStringToFile(file,readExcel(is), "utf-8");
            System.out.println("生成sql文件："+file.getAbsolutePath());
		} catch (FileNotFoundException e) {
			System.out.println("未找到指定路径的文件!");
			e.printStackTrace();
		}catch (IOException e) {
            e.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }
	}


}